#!/use/bin/python
# coding=utf-8
# 数据分析-助眠功能-用户使用情况
import datetime
import pymysql
from _decimal import Decimal

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数

    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num



#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if(X is None or Y is None):
        return rate
    if Y != 0:
        rate = round(X / Y, 2)
    return rate

def saFinish():
    try:
        conn, cur = getDataConnection()
        # 查询助眠功能执行日志
        selectSql = f"select t.active_id, t.aa_id,ifnull(JSON_EXTRACT(msg,'$.duration')/1000,0) duration  from log_common_service_{yyMMddDate} t where t.`key` = 'saFinish' and  JSON_EXTRACT(msg,'$.duration') <= 6000000 "
        cur.execute(selectSql)
        selectResult = cur.fetchall()
        for result in selectResult:
            #设置使用时间 跟 首次使用时间
            activeId = result[0]
            aaid = result[1]
            duration = result[2]
            durationMin = round(duration / 60, 2)
            selectActiveSql = f"select id,total_use_time,avg_use_time,most_use_time,use_num from analysis_zmgn_user t where 1=1 and t.active_id = '{activeId}'"
            print(selectActiveSql)
            cur.execute(selectActiveSql)
            selectActiveResult = cur.fetchone()
            if activeId == 'null' or activeId == '' or activeId is None or selectActiveResult is None:
                selectAaidSql = f"select id,total_use_time,avg_use_time,most_use_time,use_num from analysis_zmgn_user t where 1=1 and t.aa_id = '{aaid}'"
                print(selectAaidSql)
                cur.execute(selectAaidSql)
                selectActiveResult = cur.fetchone()
                if aaid == 'null' or aaid == '' or aaid is None or  selectActiveResult is None:
                    # 插入
                    insertSql = f"""insert into analysis_zmgn_user(active_id,aa_id,use_num,first_use_date,total_use_time,avg_use_time,most_use_time) 
                                           value('{activeId}','{aaid}',1,now(),{durationMin},{durationMin},{durationMin})"""
                    cur.execute(insertSql)
                    print(f"插入助眠功能用户使用情况表:activeID:{activeId},aaid:{aaid}")
                    conn.commit()
                    continue

            id = selectActiveResult[0]
            totalUseTime = selectActiveResult[1]
            mostUseTime = selectActiveResult[3]
            useNum = selectActiveResult[4]
            useNum = useNum+1
            mostUseTime = mostUseTime if mostUseTime > durationMin else durationMin
            durationMin = Decimal(durationMin)
            totalUseTime = totalUseTime + durationMin
            avgUseTime = round(totalUseTime/useNum,2)
            updateSql = f"""update analysis_zmgn_user set use_num = {useNum},total_use_time = {totalUseTime},avg_use_time = {avgUseTime},most_use_time = {mostUseTime} ,update_time = now()  where 1=1 and  id = {id}"""
            print(updateSql)
            cur.execute(updateSql)
            print(f"更新助眠功能用户使用情况表:activeID:{activeId},aaid:{aaid}")
            conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 31
        jobName = '核心指标-睡觉提醒-推送成功率'
        tableName = 'analysis_sjtx_complete_rate'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    d = 1
    date = getdate(d)
    yyMMddDate = getdateyyMMdd(d)
    monthdate = getMonthdate(d)
    print (f"{date}日期,执行<数据分析-助眠功能-用户使用情况>报表")
    saFinish()
